/**
 * @FileName: MemberOrg.java
 * @Author
 * @Description:
 * @Date 2015年12月22日 下午4:53:35
 * @CopyRight ZTE Corporation
 */
package cn.hpclub.server.model;

import java.text.SimpleDateFormat;
import java.util.Date;

import com.jfinal.ext.plugin.tablebind.TableBind;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;

@TableBind(tableName = "member_org")
public class MemberOrg extends Model<MemberOrg>{
    private static final long     serialVersionUID = 1312507976941241024L;
    public static final MemberOrg dao              = new MemberOrg();

    public static final String    ID               = "id";
    public static final String    USER_NAME        = "user_name";
    public static final String    PASSWORD         = "password";
    public static final String    NAME             = "name";
    public static final String    MOBILE           = "mobile";
    public static final String    MEMBER_TYPE      = "member_type";
    public static final String    ORG_ID           = "org_id";
    // public static final String ORG_GUID = "org_guid";
    // public static final String CHANNEL_CODE = "org_channel_code";
    public static final String    IMPORT_DESC      = "import_desc";
    public static final String    REG_TIME         = "reg_time";
    public static final String    NICKNAME         = "nickname";
    public static final String    BIRTHDAY         = "birthday";
    public static final String    NOTE             = "note";
    public static final String    MARRY            = "marry";
    public static final String    QQ               = "qq";
    public static final String    WEIXIN           = "weixin";

    public static final String    OCCUPATION       = "occupation";
    public static final String    COMPANY          = "company";
    public static final String    SCHOOL           = "school";
    public static final String    HB_MOVIE         = "hb_movie";

    public static final String    HB_MUSIC         = "hb_music";
    public static final String    HB_AREA          = "hb_area";
    public static final String    PERSONAL_DESC    = "personal_desc";
    public static final String    MODI_DATE        = "modi_date";
    public static final String    EMP_CODE         = "emp_code";

    public static final String    ENABLED          = "enabled";
    public static final String    IS_DELETED       = "is_deleted";

    public static final int       MAX_NAME_LEN     = 80;
    public static final int       MAX_EMPCODE_LEN  = 50;
    public static final int       MAX_DESC_LEN     = 128;

    // public List<MemberOrg> getMemberOrgList(){
    // String sql = "select * from member_org where is_deleted=0";
    // return dao.find(sql);
    // }

    public Integer getOrgIdbyOrgCode(String code){
        String sql = "select organization.id from organization where organization.numcode=?";
        Record r = Db.findFirst(sql, code);
        return r == null ? null : r.getInt("id");
    }

    /**
     * @Title: getMemberOrgbyUserName
     * @Description:根据用户名获取人员信息，被软删除和禁用的也算，用于导入时可以修改激活
     * @param userName
     * @return MemberOrg
     */
    public MemberOrg getMemberOrgbyUserName(String userName){
        String sql = "select * from member_org where member_org.user_name=?";

        return dao.findFirst(sql, userName);
    }

    public MemberOrg getMemberOrgbyUserNameAndEmpCodeAndName(String userName,String empCode,String name){
        String sql = "select * from member_org where member_org.user_name=? AND emp_code=? AND name=? AND is_deleted=0 ";

        return dao.findFirst(sql, userName,empCode,name);
    }


    /**
     * @Title: getActiveMemberbyUserName
     * @Description:根据用户名获取人员信息，仅获取当前可用并且激活了的账户，用户客户端修改密码和登陆
     * @param userName
     * @return MemberOrg
     */
    public MemberOrg getActiveMemberbyUserName(String userName){
        String sql = "select * from member_org where member_org.user_name=? and is_deleted=0  and enabled=1";

        return dao.findFirst(sql, userName);
    }

    public MemberOrg getActiveMemberbyUserId(int userId){
        String sql = "select * from member_org where member_org.id=? and is_deleted=0  and enabled=1";

        return dao.findFirst(sql, userId);
    }

    public boolean modifyPasswd(String username, String passwd){
        String sql = "update member_org set password=? where user_name=?";
        int count = Db.update(sql, passwd, username);
        return count == 1 ? true : false;
    }

    /**
     * @Title: getMemberOrgById
     * @Description:TODO
     * @param userId
     * @return MemberOrg
     */
    public MemberOrg getMemberOrgById(long userId){
        String sql = "select * from member_org where member_org.id=?";

        return dao.findFirst(sql, userId);
    }

    public int getOrgIdById(int userId){
        String sql = "select org_id from member_org where member_org.id=?";

        Record r = Db.findFirst(sql, userId);
        return r == null ? 0 : r.getInt("org_id");
    }

    public boolean isExistMemberOrg(String username){
        MemberOrg member = dao.findFirst(
                                         "select * from member_org where member_org.user_name=? and is_deleted=0  and enabled=1",
                                         username);
        if(member != null){
            return true;
        } else{
            return false;
        }
    }

    public boolean exist(int userId){
        MemberOrg member = dao
                .findFirst("select * from member_org where member_org.id=? and is_deleted=0  and enabled=1", userId);
        if(member != null){
            return true;
        } else{
            return false;
        }
    }

    public Long getUserCount(){
        String sql = " select count(1) as cnt from member_org where enabled = 1 and is_deleted = 0 ";
        Record user = Db.findFirst(sql);
        Long userCnt = user.get("cnt");

        return userCnt;
    }

    public Long getDayAddUserCount(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String today = sdf.format(new Date());

        String sql = " select count(1) as cnt from member_org where enabled = 1 and is_deleted = 0 "
                + " and date_format(reg_time, '%Y-%m-%d') = '" + today + "' ";
        Record user = Db.findFirst(sql);
        Long userCnt = user.get("cnt");

        return userCnt;
    }

    /**
     * @Title: getMemberOrgInfoByUserId
     * @Description:根据userId获取所属地市所对应的客服工号和员工工号等信息
     * @param userId
     * @return MemberOrg
     */
    public MemberOrg getMemberOrgInfoByUserId(long userId){
        String sql = "select mem.id as userId,mem.emp_code,org2.NumCode as group_id,city.login_no,city.region_id,org.DisplayName as orgName from "
                + Constant.Table.MEMBER_ORG + " mem left join " + Constant.Table.ORGANIZATION
                + " org on mem.org_id=org.id left join " + Constant.Table.ORGANIZATION
                + " org2 on org2.id=org.CityCode left join " + Constant.Table.CMCC_CITY
                + " city on city.group_id=org2.NumCode where mem.id=?";
        // System.out.println("sql=" + sql);
        return dao.findFirst(sql, userId);
    }

    public String getOrgNumCodebyUserid(int userId){
        String sql = "select organization.NumCode as num_code from organization,member_org where member_org.id = ? and member_org.org_id=organization.id";
        Record r = Db.findFirst(sql, userId);
        return r == null ? "" : r.getStr("num_code");
    }

    /**
     * @Title: getMemberOrgInfoByToken
     * @Description:TODO
     * @param token
     * @return Record
     */
    public Record getMemberOrgInfoByToken(String token){
        String sql = "select mem.emp_code,org.NumCode,city.region_id_3 from member_org mem left join organization org on org.id=mem.org_id left join cmcc_city city on city.group_id=(select org2.NumCode from organization org2 where org2.id=org.CityCode) where mem.user_name=(select utm.username from user_token_map utm where utm.token=?)";

        return Db.findFirst(sql, token);
    }

    /**
     * 
     * @Title: isAccountsManager
     * @Description:根据userId判断该用户是否客户经理
     * @param userId
     * @return Record
     */
    public Record isAccountsManager(String userId){
        int topId = InterfaceUtil.getIdTop();
        String sql = "SELECT member_org.id AS id, user_name, numcode AS org_channel_code, emp_code, displayname, class_name, IF ( orgtype.orgidtmp || orgtype2.orgidtmp2, '1', '0' ) AS is_accounts_manager FROM member_org, organization_class org_class, organization org LEFT JOIN ( SELECT id AS orgidtmp FROM `organization` WHERE ( parentid = "
                + topId + " OR parentid IN ( SELECT id FROM organization WHERE parentid = " + topId
                + " ))) AS orgtype ON org.id = orgtype.orgidtmp LEFT JOIN ( SELECT org.id AS orgidtmp2, orgcls.websiteid AS websiteidtype FROM organization org, organization_class orgcls WHERE org.websiteid = orgcls.id AND org.enabled = 1 AND orgcls.enabled = 1 AND ( orgcls.websiteid LIKE '6%' OR orgcls.websiteid = '1000' OR orgcls.websiteid = '4800' )) AS orgtype2 ON org.id = orgtype2.orgidtmp2 WHERE member_org.id = ? AND member_org.enabled = 1 AND member_org.org_id = org.id AND org.websiteid = org_class.id AND member_org.is_deleted = 0";
        // System.out.println("isAccountsManager sql = " + sql);
        return Db.findFirst(sql, userId);
    }

    /**
     * @Title: getUserNameByUserId
     * @Description:TODO
     * @param userId
     * @return String
     */
    public String getUserNameByUserId(String userId){
        String sql = "select user_name from member_org where id=?";
        Record r = Db.findFirst(sql, userId);
        return r == null ? "" : r.getStr("user_name");
    }
}
